package com.tnsw.noxnox.db;

import java.util.ArrayList;
import java.util.Hashtable;

import com.tnsw.coreutils.AsyncManager;
import com.tnsw.noxnox.db.tables.DownloadProgress;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

public class DBManager extends DBManagerBase {

	private static boolean sIsRunning = false;
	private static DBManager sInstance;
	private AsyncManager asyncManager;
	
	private DBManagerInterface dbi;
	public DBManagerInterfaceDispatcher DBDispatcher;
	
	public DBManager(Context context) {
		super(context);
		this.tag = "DBManager";
		this.asyncManager = AsyncManager.getInstance();
		
		sIsRunning = true;
		sInstance = this;
		
		this.DBDispatcher = new DBManagerInterfaceDispatcher();
		this.setListener(this.DBDispatcher);
	}
	
	public static DBManager getInstance(Context context) {
		if (sIsRunning == false) {
			sInstance = new DBManager(context);
		}
		
		return(sInstance);
	}
	
	public void setListener(DBManagerInterface dbi) {
		this.dbi = dbi;
	}
	
	public DBManagerInterface getListener() {
		return(this.dbi);
	}
	
	public String getDownloadAttributeValue(int primarykey, String key) {
		String value = null;
		SQLiteDatabase db = null;
		
		try {
			db = this.getReadableDatabase();
			String sql = "select * from download_attrs where download_progress=? and attr_key=?";
			Cursor cursor = db.rawQuery(sql, new String[] {String.valueOf(primarykey), key});
			cursor.moveToFirst();
			while (!cursor.isAfterLast()) {
				value = cursor.getString(cursor.getColumnIndexOrThrow("attr_value"));
				cursor.moveToNext();
			}
			
		} catch (Exception e) {
			Log.e(tag, "getAttributeValue(primarykey=" + String.valueOf(primarykey) + ", key=" + key + ") - " + e.toString(), e);
		}

		if (db != null) {
			db.close();
		}		
		
		return(value);
	}
	
	public ArrayList<DownloadProgress> getDownloadProgress(String whereClause, String[] values, String orderby) {
		SQLiteDatabase db = null;
		
		try {
			db = this.getReadableDatabase();
			String sql = "select * from download_progress ";
			if (whereClause != null) {
				if (whereClause.equals("") ==  false) {
					sql = sql + " where " + whereClause;
				}	
			}
			
			if (orderby != null) {
				if (orderby.equals("") == false) {
					sql = sql + orderby;
				}
			}
			
			this.asyncManager.appendNetworkLog("sql=" + sql);
			
			Cursor cursor = db.rawQuery(sql, values);
			ArrayList<DownloadProgress> a = new ArrayList<DownloadProgress>();
			
			cursor.moveToFirst();
			while (!cursor.isAfterLast()) {
				DownloadProgress dp = new DownloadProgress();
				dp.content_length = cursor.getInt(cursor.getColumnIndexOrThrow("content_length"));
				dp.current_position = cursor.getInt(cursor.getColumnIndexOrThrow("current_position"));
				dp.duration_sec = cursor.getInt(cursor.getColumnIndexOrThrow("duration_sec"));
				dp.filename = cursor.getString(cursor.getColumnIndexOrThrow("filename"));
				dp.primarykey = cursor.getInt(cursor.getColumnIndexOrThrow("primarykey"));
				dp.status = cursor.getInt(cursor.getColumnIndexOrThrow("status"));
				dp.tag_key = cursor.getString(cursor.getColumnIndexOrThrow("tag_key"));
				dp.tag_value = cursor.getString(cursor.getColumnIndexOrThrow("tag_value"));
				dp.thumbnail_filename = cursor.getString(cursor.getColumnIndexOrThrow("thumbnail_filename"));
				dp.title = cursor.getString(cursor.getColumnIndexOrThrow("title"));
				dp.url = cursor.getString(cursor.getColumnIndexOrThrow("url"));
				dp.video_sites = cursor.getInt(cursor.getColumnIndexOrThrow("video_sites"));
				
				a.add(dp);
				
				cursor.moveToNext();
			}
			
			cursor.close();
			db.close();
			
			return(a);
		} catch (Exception e) {
			Log.e(tag, "getDownloadProgress() - " + e.toString(), e);
		}
		
		if (db != null) {
			
			db.close();
			
		}
		
		return(null);
	}
	
	public void updateDownloadProgress(DownloadProgress dp, boolean raiseEvent) {
		SQLiteDatabase db = null;
		ContentValues values = new ContentValues();
		
		try {
			db = this.getWritableDatabase();
			
			values.put("video_sites", dp.video_sites);
			values.put("url", dp.url);
			values.put("content_length", dp.content_length);
			values.put("tag_key", dp.tag_key);
			values.put("tag_value", dp.tag_value);
			values.put("current_position", dp.current_position);
			values.put("status", dp.status);
			values.put("filename", dp.filename);
			values.put("duration_sec", dp.duration_sec);
			values.put("thumbnail_filename", dp.thumbnail_filename);
			values.put("final_url", dp.final_url);
			
			db.update("download_progress", values, "primarykey=" + String.valueOf(dp.primarykey), null);
			
		
			if ( (this.dbi != null) && (raiseEvent == true) ) {
				dbi.onSQLExecuted(this, "update", "download_progress");
			}
					
		} catch (Exception e) {
			Log.e(tag, "updateDownloadProgress(" + dp.toString() + ") - " + e.toString(), e);
		}
		
		if (db != null) {
			if (db.isOpen() == true) {
				db.close();
			}
		}
	}
	
	public void updateDownloadAttribute(SQLiteDatabase db, int downloadProgress, Hashtable<String, String> h) {
		if (h == null) { return; }
				
		boolean hasExistingConnection = true;
		
		if (db == null) {
			db = this.getWritableDatabase();
			//db.beginTransaction();
			hasExistingConnection = false;
		}
		
		try {
			for (String key : h.keySet()) {
				String value = h.get(key);
				Log.d(tag, "Checking key=" + key + ", value=" + value + ", dp=" + String.valueOf(downloadProgress));
				Cursor cursor = db.rawQuery("select * from download_attrs where download_progress=? and attr_key=? and attr_value=?", new String[] {String.valueOf(downloadProgress), key, value});
				
				if (cursor.getCount() == 0) {
					cursor.close();
					Log.d(tag, "> Not found..");
					ContentValues values = new ContentValues();
					int primarykey = this.getNewPrimarykey();
					values.put("primarykey", primarykey);
					values.put("download_progress", downloadProgress);
					values.put("attr_key", key);
					if (value.equals("")) {
						values.putNull("attr_value");
					} else {
						values.put("attr_value", value);	
					}
								
					long insertId = db.insert("download_attrs", null, values);
					Log.d(tag, "> inserted, id=" + String.valueOf(insertId));
				} else {  // found
					cursor.close();
				}
				

				
			}
			
			if (hasExistingConnection == false) {
				//db.setTransactionSuccessful();
				//db.endTransaction();
			}
						
		} catch (Exception e) {
			Log.e(tag, "updateDownloadAttribute() - " + e.toString(), e);
			if (db != null) {
				if (db.inTransaction() == true) {
					//db.endTransaction();
				}
			}
		}
		
		if (db != null) {
			db.close();
		}
		
	}
	
	public int deleteDownloadProgress(DownloadProgress dp) {
		SQLiteDatabase db = null;
		
		try {
			db = this.getWritableDatabase();
			int i = db.delete("download_progress", "primarykey=?", new String[] {String.valueOf(dp.primarykey)});
			i = db.delete("download_attrs", "download_progress=?", new String[] {String.valueOf(dp.primarykey)});
			return(i);
			
		} catch (Exception e) {
			Log.e(tag, "deleteDownloadProgress(dp=" + dp.primarykey + ") - " + e.toString(), e);
		}
		
		if (db != null) {
			if (db.isOpen() == true) {
				db.close();
			}
		}
		
		return(0);
	}
	
	public int insertDownloadProgress(DownloadProgress dp) {
		SQLiteDatabase db = null;
		ContentValues values = new ContentValues();
		
		try {
			db = this.getWritableDatabase();
			
			if (dp.primarykey == 0) {
				int primarykey = DBManager.getInstance(this.context).getNewPrimarykey();
				dp.primarykey = primarykey;	
			}
			
			values.put("primarykey", dp.primarykey);
			values.put("video_sites", dp.video_sites);
			values.put("url", dp.url);
			values.put("content_length", dp.content_length);
			values.put("tag_key", dp.tag_key);
			values.put("tag_value", dp.tag_value);
			values.put("current_position", dp.current_position);
			values.put("status", dp.status);
			values.put("title", dp.title);
			values.put("filename", dp.filename);
			values.put("duration_sec", dp.duration_sec);
			values.put("thumbnail_filename", dp.thumbnail_filename);
			values.put("final_url", dp.final_url);
			values.putNull("lastupd");
			
			long insertId = db.insert("download_progress", null, values);
			Log.d(tag, "inserted into download_progress(" + dp.toString() + "), id=" + insertId);
			
			if (this.dbi != null) {
				dbi.onSQLExecuted(this, "insert", "download_progress");
			}
			
		} catch (Exception e) {
			Log.e(tag, "insertDownloadProgress(" + dp.toString() + ") - " + e.toString(), e);
		}
		
		if (db != null) {
			if (db.isOpen() == true) {
				db.close();
			}
		}
		
		return(dp.primarykey);
 
	}

}
